# 频道管理服务
"""
频道管理相关的业务逻辑服务

提供频道的CRUD操作、搜索、状态管理等功能
"""

from datetime import datetime, date
from decimal import Decimal, InvalidOperation
from typing import List, Optional, Dict, Any
from sqlalchemy.exc import IntegrityError
from sqlalchemy import and_, desc
from models import db, Channel, ChannelData
from config.channel_defaults import get_default_cpm, DEFAULT_CPM

class ChannelService:
    """频道管理服务类"""

    @staticmethod
    def _normalize_category(category: Optional[str]) -> Optional[str]:
        """标准化频道分类字符串"""
        if category is None:
            return None
        normalized = category.strip()
        return normalized or None

    @staticmethod
    def _ensure_decimal(value: Optional[float]) -> Optional[Decimal]:
        """将输入值安全转换为Decimal，失败时返回None"""
        if value is None:
            return None
        try:
            return Decimal(str(value)).quantize(Decimal('0.01'))
        except (InvalidOperation, ValueError, TypeError):
            return None

    @staticmethod
    def _resolve_cpm(category: Optional[str], cpm: Optional[float], enforce_default: bool = True) -> Decimal:
        """根据分类计算CPM，必要时强制使用分类默认值"""
        normalized_category = ChannelService._normalize_category(category)
        default_cpm = get_default_cpm(normalized_category)

        if enforce_default and normalized_category:
            return default_cpm

        converted = ChannelService._ensure_decimal(cpm)
        if converted is None or converted <= 0:
            return default_cpm

        return converted

    @staticmethod
    def _enrich_channels_with_latest_data(channels: List[Channel]) -> List[Dict[str, Any]]:
        """为频道列表添加最新数据，使用优化的批量查询避免N+1问题
        
        Args:
            channels: 频道对象列表
            
        Returns:
            List[Dict]: 包含latest_data字段的频道字典列表
        """
        if not channels:
            return []
        
        # 批量获取所有频道的最新数据，避免N+1查询
        channel_ids = [channel.id for channel in channels]
        
        # 使用子查询找到每个频道的最新日期
        latest_dates_subquery = db.session.query(
            ChannelData.channel_id,
            db.func.max(ChannelData.date).label('max_date')
        ).filter(
            ChannelData.channel_id.in_(channel_ids)
        ).group_by(ChannelData.channel_id).subquery()
        
        # 获取每个频道的最新数据记录
        latest_data_query = db.session.query(ChannelData).join(
            latest_dates_subquery,
            and_(
                ChannelData.channel_id == latest_dates_subquery.c.channel_id,
                ChannelData.date == latest_dates_subquery.c.max_date
            )
        ).all()
        
        # 创建映射字典以快速查找
        latest_data_map = {data.channel_id: data for data in latest_data_query}
            
        # 构建结果
        result = []
        for channel in channels:
            channel_dict = channel.to_dict()
            latest_data = latest_data_map.get(channel.id)
            channel_dict['latest_data'] = latest_data.to_dict() if latest_data else None
            result.append(channel_dict)
            
        return result
    
    @staticmethod
    def create_channel(user_id: int, name: str, channel_id: str, url: str, 
                      cpm: float = 1.00, category: str = None, sort: int = 0) -> Dict[str, Any]:
        """创建新频道
        
        Args:
            user_id: 用户ID
            name: 频道名称
            channel_id: YouTube频道ID
            url: 频道URL
            cpm: CPM值，默认1.00
            category: 频道分类，可选
            sort: 排序值，默认0
            
        Returns:
            Dict: 包含结果状态和数据的字典
        """
        try:
            # 检查用户下是否已存在相同频道
            existing = Channel.query.filter_by(user_id=user_id, channel_id=channel_id).first()
            if existing:
                return {
                    'success': False,
                    'message': f'您已添加过频道 {channel_id}',
                    'data': None
                }
            
            normalized_category = ChannelService._normalize_category(category)
            resolved_cpm = ChannelService._resolve_cpm(normalized_category, cpm, enforce_default=True)

            # 创建新频道
            channel = Channel(
                user_id=user_id,
                name=name.strip() if isinstance(name, str) else name,
                channel_id=channel_id,
                url=url,
                cpm=resolved_cpm,
                category=normalized_category,
                sort=sort
            )
            
            db.session.add(channel)
            db.session.commit()
            
            return {
                'success': True,
                'message': '频道创建成功',
                'data': channel.to_dict()
            }
            
        except IntegrityError as e:
            db.session.rollback()
            return {
                'success': False,
                'message': f'数据库约束错误: {str(e)}',
                'data': None
            }
        except Exception as e:
            db.session.rollback()
            return {
                'success': False,
                'message': f'创建频道失败: {str(e)}',
                'data': None
            }
    
    @staticmethod
    def get_user_channels(user_id: int, active_only: bool = True, order_by_sort: bool = False) -> List[Dict[str, Any]]:
        """获取用户的频道列表
        
        Args:
            user_id: 用户ID
            active_only: 是否只返回活跃频道，默认True
            order_by_sort: 是否按sort字段排序，默认False
            
        Returns:
            List[Dict]: 频道列表，包含latest_data字段
        """
        try:
            query = Channel.query.filter_by(user_id=user_id)
            if active_only:
                query = query.filter_by(is_active=True)
            
            # 选择排序方式
            if order_by_sort:
                channels = query.order_by(Channel.sort.asc(), Channel.id.asc()).all()
            else:
                channels = query.order_by(Channel.created_at.desc()).all()
            
            if not channels:
                return []
            
            # 批量获取所有频道的最新数据，避免N+1查询
            channel_ids = [channel.id for channel in channels]
            
            # 使用子查询找到每个频道的最新日期
            latest_dates_subquery = db.session.query(
                ChannelData.channel_id,
                db.func.max(ChannelData.date).label('max_date')
            ).filter(
                ChannelData.channel_id.in_(channel_ids)
            ).group_by(ChannelData.channel_id).subquery()
            
            # 获取每个频道的最新数据记录
            latest_data_query = db.session.query(ChannelData).join(
                latest_dates_subquery,
                and_(
                    ChannelData.channel_id == latest_dates_subquery.c.channel_id,
                    ChannelData.date == latest_dates_subquery.c.max_date
                )
            ).all()
            
            # 创建映射字典以快速查找
            latest_data_map = {data.channel_id: data for data in latest_data_query}
                
            # 构建结果
            result = []
            for channel in channels:
                channel_dict = channel.to_dict()
                latest_data = latest_data_map.get(channel.id)
                channel_dict['latest_data'] = latest_data.to_dict() if latest_data else None
                result.append(channel_dict)
                
            return result
            
        except Exception as e:
            print(f"获取用户频道列表失败: {e}")
            return []
    
    @staticmethod
    def get_all_channels(active_only: bool = True, order_by_sort: bool = False) -> List[Dict[str, Any]]:
        """获取所有频道（管理员用）
        
        Args:
            active_only: 是否只返回活跃频道，默认True
            order_by_sort: 是否按sort字段排序，默认False
            
        Returns:
            List[Dict]: 频道列表，包含latest_data字段
        """
        try:
            query = Channel.query
            if active_only:
                query = query.filter_by(is_active=True)
            
            # 选择排序方式
            if order_by_sort:
                channels = query.order_by(Channel.sort.asc(), Channel.id.asc()).all()
            else:
                channels = query.order_by(Channel.created_at.desc()).all()
            
            if not channels:
                return []
            
            # 批量获取所有频道的最新数据，避免N+1查询
            channel_ids = [channel.id for channel in channels]
            
            # 使用子查询找到每个频道的最新日期
            latest_dates_subquery = db.session.query(
                ChannelData.channel_id,
                db.func.max(ChannelData.date).label('max_date')
            ).filter(
                ChannelData.channel_id.in_(channel_ids)
            ).group_by(ChannelData.channel_id).subquery()
            
            # 获取每个频道的最新数据记录
            latest_data_query = db.session.query(ChannelData).join(
                latest_dates_subquery,
                and_(
                    ChannelData.channel_id == latest_dates_subquery.c.channel_id,
                    ChannelData.date == latest_dates_subquery.c.max_date
                )
            ).all()
            
            # 创建映射字典以快速查找
            latest_data_map = {data.channel_id: data for data in latest_data_query}
                
            # 构建结果
            result = []
            for channel in channels:
                channel_dict = channel.to_dict()
                latest_data = latest_data_map.get(channel.id)
                channel_dict['latest_data'] = latest_data.to_dict() if latest_data else None
                result.append(channel_dict)
                
            return result
            
        except Exception as e:
            print(f"获取频道列表失败: {e}")
            return []
    
    @staticmethod
    def get_channel_by_id(channel_id: int, user_id: int = None) -> Optional[Dict[str, Any]]:
        """根据ID获取频道
        
        Args:
            channel_id: 频道数据库ID
            user_id: 用户ID，如果提供则检查权限
            
        Returns:
            Optional[Dict]: 频道信息或None
        """
        try:
            query = Channel.query
            if user_id is not None:
                query = query.filter_by(user_id=user_id)
            
            channel = query.filter_by(id=channel_id).first()
            return channel.to_dict() if channel else None
        except Exception as e:
            print(f"获取频道失败: {e}")
            return None
    
    @staticmethod
    def get_channel_by_youtube_id(youtube_id: str, user_id: int = None) -> Optional[Dict[str, Any]]:
        """根据YouTube频道ID获取频道
        
        Args:
            youtube_id: YouTube频道ID
            user_id: 用户ID，如果提供则检查权限
            
        Returns:
            Optional[Dict]: 频道信息或None
        """
        try:
            query = Channel.query.filter_by(channel_id=youtube_id)
            if user_id is not None:
                query = query.filter_by(user_id=user_id)
            
            channel = query.first()
            return channel.to_dict() if channel else None
        except Exception as e:
            print(f"获取频道失败: {e}")
            return None
    
    @staticmethod
    def update_channel(channel_id: int, user_id: int = None, **kwargs) -> Dict[str, Any]:
        """更新频道信息

        Args:
            channel_id: 频道数据库ID
            user_id: 用户ID，如果提供则检查权限
            **kwargs: 要更新的字段
            
        Returns:
            Dict: 更新结果
        """
        try:
            query = Channel.query
            if user_id is not None:
                query = query.filter_by(user_id=user_id)
            
            channel = query.filter_by(id=channel_id).first()
            if not channel:
                return {
                    'success': False,
                    'message': '频道不存在或无权限访问',
                    'data': None
                }

            has_updates = False

            if 'name' in kwargs:
                raw_name = kwargs['name'] or ''
                name = raw_name.strip()
                if not name:
                    raise ValueError('频道名称不能为空')
                if channel.name != name:
                    channel.name = name
                    has_updates = True

            if 'url' in kwargs and kwargs['url']:
                new_url = kwargs['url'].strip()
                if new_url and channel.url != new_url:
                    channel.url = new_url
                    has_updates = True

            if 'sort' in kwargs and kwargs['sort'] is not None:
                try:
                    new_sort = int(kwargs['sort'])
                except (ValueError, TypeError):
                    new_sort = channel.sort
                if channel.sort != new_sort:
                    channel.sort = new_sort
                    has_updates = True

            if 'is_active' in kwargs:
                new_state = bool(kwargs['is_active'])
                if channel.is_active != new_state:
                    channel.is_active = new_state
                    has_updates = True

            normalized_category: Optional[str] = None
            if 'category' in kwargs:
                normalized_category = ChannelService._normalize_category(kwargs['category'])
                if channel.category != normalized_category:
                    channel.category = normalized_category
                    has_updates = True

            if 'cpm' in kwargs or normalized_category is not None:
                target_category = channel.category
                cpm_candidate = kwargs.get('cpm', channel.cpm)
                resolved_cpm = ChannelService._resolve_cpm(target_category, cpm_candidate, enforce_default=True)
                current_cpm = ChannelService._ensure_decimal(channel.cpm)
                if current_cpm is None or current_cpm != resolved_cpm:
                    channel.cpm = resolved_cpm
                    has_updates = True

            if has_updates:
                channel.updated_at = datetime.utcnow()
                db.session.commit()

            return {
                'success': True,
                'message': '频道更新成功',
                'data': channel.to_dict()
            }

        except Exception as e:
            db.session.rollback()
            return {
                'success': False,
                'message': f'更新频道失败: {str(e)}',
                'data': None
            }
    
    @staticmethod
    def delete_channel(channel_id: int, user_id: int = None) -> Dict[str, Any]:
        """删除频道（软删除 - 设为非活跃）
        
        Args:
            channel_id: 频道数据库ID
            user_id: 用户ID，如果提供则检查权限
            
        Returns:
            Dict: 删除结果
        """
        try:
            query = Channel.query
            if user_id is not None:
                query = query.filter_by(user_id=user_id)
            
            channel = query.filter_by(id=channel_id).first()
            if not channel:
                return {
                    'success': False,
                    'message': '频道不存在或无权限访问',
                    'data': None
                }
            
            # 软删除：设为非活跃状态
            channel.is_active = False
            channel.updated_at = datetime.utcnow()
            db.session.commit()
            
            return {
                'success': True,
                'message': '频道已停用',
                'data': channel.to_dict()
            }
            
        except Exception as e:
            db.session.rollback()
            return {
                'success': False,
                'message': f'停用频道失败: {str(e)}',
                'data': None
            }

    @staticmethod
    def sync_category_defaults(user_id: Optional[int] = None, commit: bool = True) -> Dict[str, int]:
        """批量同步频道CPM为分类默认值"""
        try:
            query = Channel.query
            if user_id is not None:
                query = query.filter_by(user_id=user_id)

            channels = query.all()
            updated = 0

            for channel in channels:
                normalized_category = ChannelService._normalize_category(channel.category)
                resolved_cpm = ChannelService._resolve_cpm(normalized_category, channel.cpm, enforce_default=True)
                current_cpm = ChannelService._ensure_decimal(channel.cpm) or DEFAULT_CPM
                if current_cpm != resolved_cpm:
                    channel.cpm = resolved_cpm
                    channel.updated_at = datetime.utcnow()
                    updated += 1

            if commit:
                if updated:
                    db.session.commit()
            else:
                db.session.rollback()

            return {
                'total': len(channels),
                'updated': updated
            }
        except Exception:
            db.session.rollback()
            raise

    @staticmethod
    def search_user_channels(user_id: int, keyword: str, active_only: bool = True, order_by_sort: bool = False) -> List[Dict[str, Any]]:
        """搜索用户的频道
        
        Args:
            user_id: 用户ID
            keyword: 搜索关键词
            active_only: 是否只搜索活跃频道
            order_by_sort: 是否按sort字段排序，默认False
            
        Returns:
            List[Dict]: 匹配的频道列表
        """
        try:
            query = Channel.query.filter(
                Channel.user_id == user_id,
                Channel.name.ilike(f'%{keyword}%')
            )
            
            if active_only:
                query = query.filter_by(is_active=True)
            
            # 选择排序方式
            if order_by_sort:
                channels = query.order_by(Channel.sort.asc(), Channel.id.asc()).all()
            else:
                channels = query.order_by(Channel.created_at.desc()).all()
                
            return ChannelService._enrich_channels_with_latest_data(channels)
            
        except Exception as e:
            print(f"搜索用户频道失败: {e}")
            return []
    
    @staticmethod
    def search_channels(keyword: str, active_only: bool = True, order_by_sort: bool = False) -> List[Dict[str, Any]]:
        """搜索所有频道（管理员用）
        
        Args:
            keyword: 搜索关键词
            active_only: 是否只搜索活跃频道
            order_by_sort: 是否按sort字段排序，默认False
            
        Returns:
            List[Dict]: 匹配的频道列表
        """
        try:
            query = Channel.query.filter(
                Channel.name.ilike(f'%{keyword}%')
            )
            
            if active_only:
                query = query.filter_by(is_active=True)
            
            # 选择排序方式
            if order_by_sort:
                channels = query.order_by(Channel.sort.asc(), Channel.id.asc()).all()
            else:
                channels = query.order_by(Channel.created_at.desc()).all()
                
            return ChannelService._enrich_channels_with_latest_data(channels)
            
        except Exception as e:
            print(f"搜索频道失败: {e}")
            return []
    
    @staticmethod
    def get_user_channels_by_category(user_id: int, category: str, active_only: bool = True, order_by_sort: bool = False) -> List[Dict[str, Any]]:
        """根据分类获取用户的频道
        
        Args:
            user_id: 用户ID
            category: 频道分类
            active_only: 是否只返回活跃频道
            order_by_sort: 是否按sort字段排序，默认False
            
        Returns:
            List[Dict]: 频道列表
        """
        try:
            query = Channel.query.filter_by(user_id=user_id, category=category)
            
            if active_only:
                query = query.filter_by(is_active=True)
            
            # 选择排序方式
            if order_by_sort:
                channels = query.order_by(Channel.sort.asc(), Channel.id.asc()).all()
            else:
                channels = query.order_by(Channel.created_at.desc()).all()
                
            return ChannelService._enrich_channels_with_latest_data(channels)
            
        except Exception as e:
            print(f"获取用户分类频道失败: {e}")
            return []
    
    @staticmethod
    def get_channels_by_category(category: str, active_only: bool = True, order_by_sort: bool = False) -> List[Dict[str, Any]]:
        """根据分类获取所有频道（管理员用）
        
        Args:
            category: 频道分类
            active_only: 是否只返回活跃频道
            order_by_sort: 是否按sort字段排序，默认False
            
        Returns:
            List[Dict]: 频道列表
        """
        try:
            query = Channel.query.filter_by(category=category)
            
            if active_only:
                query = query.filter_by(is_active=True)
            
            # 选择排序方式
            if order_by_sort:
                channels = query.order_by(Channel.sort.asc(), Channel.id.asc()).all()
            else:
                channels = query.order_by(Channel.created_at.desc()).all()
                
            # 为每个频道添加latest_data字段
            result = []
            for channel in channels:
                channel_dict = channel.to_dict()
                
                # 获取频道的最新数据
                latest_data = ChannelData.query.filter_by(
                    channel_id=channel.id
                ).order_by(ChannelData.date.desc()).first()
                
                channel_dict['latest_data'] = latest_data.to_dict() if latest_data else None
                result.append(channel_dict)
                
            return result
            
        except Exception as e:
            print(f"获取分类频道失败: {e}")
            return []
    
    @staticmethod
    def get_channel_statistics(channel_id: int, user_id: int = None) -> Dict[str, Any]:
        """获取频道统计信息
        
        Args:
            channel_id: 频道数据库ID
            user_id: 用户ID，如果提供则检查权限
            
        Returns:
            Dict: 统计信息
        """
        try:
            query = Channel.query
            if user_id is not None:
                query = query.filter_by(user_id=user_id)
            
            channel = query.filter_by(id=channel_id).first()
            if not channel:
                return {'success': False, 'message': '频道不存在或无权限访问'}
            
            # 获取最新数据
            latest_data = ChannelData.query.filter_by(
                channel_id=channel_id
            ).order_by(ChannelData.date.desc()).first()
            
            # 获取数据记录总数
            total_records = ChannelData.query.filter_by(channel_id=channel_id).count()
            
            # 获取最早和最晚数据日期
            earliest = ChannelData.query.filter_by(
                channel_id=channel_id
            ).order_by(ChannelData.date.asc()).first()
            
            # 计算总收入
            total_revenue = db.session.query(
                db.func.sum(ChannelData.estimated_revenue)
            ).filter_by(channel_id=channel_id).scalar() or 0
            
            return {
                'success': True,
                'data': {
                    'channel': channel.to_dict(),
                    'latest_data': latest_data.to_dict() if latest_data else None,
                    'total_records': total_records,
                    'earliest_date': earliest.date.isoformat() if earliest else None,
                    'latest_date': latest_data.date.isoformat() if latest_data else None,
                    'total_revenue': float(total_revenue)
                }
            }
            
        except Exception as e:
            return {
                'success': False,
                'message': f'获取统计信息失败: {str(e)}'
            }
